<?php
class Default_Model_DbTable_Recipes extends Default_Model_DbTable_DbTableEntityAbstract
{
	protected $_primary = 'recipe_id';
	protected $_db; 
	
    public function init()
    {
    	$this->_db = Zend_Db_Table::getDefaultAdapter();
    	$this->_name = 'recipes';
    }
    
    public function addRecipe($data)
    {
    	if (!$this->insert($data)) {
    		return false;
    	}
    	return $this->getAdapter()->lastInsertId();
    }
    
    public function getRecipeById($recipeId)
    {
    	$select = $this->select()
    	               ->where("recipe_id = ?", $recipeId);
    	$result = $this->fetchRow($select);
    	return $result;
    }
    
    public function updateRecipe($data, $id)
    {
		$where = $this->getAdapter()->quoteInto('recipe_id = ?', $id);
		$this->update($data, $where);
    }
    
    public function getRecipesList($foodType, $limit, $offset)
    {
    	$select = $this->selectAllRecipes();
    	
    	$select->limit($limit, $offset)
    		   ->from(array('r' => 'recipes'))
    		   ->order(array('r.recipe_id DESC'));
    	
    	if ($foodType !== "all") {			   
    		$select->where("ft.food_type_latname = ?", $foodType);
    	}
    	$result = $this->_db->fetchAll($select);
    	
    	return $result;
    }
    
    public function getRecipesByIngredient($ingredient, $limit, $offset)
    {
    	$select = $this->_db->select()
    				   ->join(
    				       array('rhi' => 'recipe_has_ingredients'),
    				       'r.recipe_id = rhi.recipe_id'
    				   )
    				   ->join(
    				       array('i' => 'ingredients'),
    				       'rhi.ingredient_id = i.ingredient_id'
    				   );
    	
    	$select->limit($limit, $offset)
    		   ->from(array('r' => 'recipes'));
    			   
    	$select->where("rhi.is_main = 1 AND i.ingredient_name = ?", $ingredient);
    	
    	$result = $this->_db->fetchAll($select);
    	
    	return $result;
    }
    
    public function getRecipesCount($foodType)
    {
    	$select = $this->selectAllRecipes()
    				   ->from(
    				       array('r' => 'recipes'),
    				       array('count' => 'COUNT(*)')
    				   );
    				   
    	if ($foodType !== "all") {			   
    		$select->where("ft.food_type_name = ?", $foodType);
    	}
    				   
		$result = $this->_db->fetchOne($select);
    	return $result;
    }
    
    public function getRecipesCountByIngredient($ingredient)
    {
    	$select = $this->_db->select()
    				   ->join(
    				       array('rhi' => 'recipe_has_ingredients'),
    				       'r.recipe_id = rhi.recipe_id'
    				   )
    				   ->join(
    				       array('i' => 'ingredients'),
    				       'rhi.ingredient_id = i.ingredient_id'
    				   )
    				   ->from(
    				       array('r' => 'recipes'),
    				       array('count' => 'COUNT(*)')
    				   )
    				   ->where("i.ingredient_name = ?", $ingredient);
    	
    	$result = $this->_db->fetchOne($select);
    	return $result;
    }
    
    private function selectAllRecipes()
    {
    	$select = $this->_db->select()
    				   ->join(
    				       array('rhf' => 'recipe_has_food_type'),
    				       'r.recipe_id = rhf.recipe_id'
    				   )
    				   ->join(
    				       array('ft' => 'food_types'),
    				       'rhf.food_type_id = ft.food_type_id'
    				   );
		
    	return $select;
    }
}